﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient
Public Class ClientesComDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"

    Public Function existeClienteCom(ByVal pIdCli As Integer) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM clientescom " & _
                                            "WHERE IdCli = ?IdCli;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?IdCli", pIdCli)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function registraClienteCom(ByVal pClienteCom As ClientesComBE) As Boolean

        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO clientescom (IdCli, LimCre, Saldo, DiasCred, MetPag, Banco, " & _
                                                                    "CuentaBan, CliMay, ListPrec, Descuento, Clasificacion, Vendedor, CtaCont, CtaAnt) " & _
                                            "VALUES (@IdCli, @LimCre, @Saldo, @DiasCred, @MetPag, @Banco, " & _
                                                    "@CuentaBan, @CliMay, @ListPrec, @Desc, @Clasificacion, @Vendedor, @CtaCont, @CtaAnt);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@IdCli", pClienteCom.IdCli)
                    myComm.Parameters.AddWithValue("@LimCre", pClienteCom.LimiteCredito)
                    myComm.Parameters.AddWithValue("@Saldo", pClienteCom.Saldo)
                    myComm.Parameters.AddWithValue("@DiasCred", pClienteCom.DiasCredito)
                    If (pClienteCom.MetodoPago IsNot Nothing) Then
                        myComm.Parameters.AddWithValue("@MetPag", pClienteCom.MetodoPago.IdMetodP)
                    Else
                        myComm.Parameters.AddWithValue("@MetPag", DBNull.Value)
                    End If
                    myComm.Parameters.AddWithValue("@Banco", pClienteCom.Banco)
                    myComm.Parameters.AddWithValue("@CuentaBan", pClienteCom.CuentaBancaria)
                    myComm.Parameters.AddWithValue("@CliMay", pClienteCom.ClienteMayoreo)
                    myComm.Parameters.AddWithValue("@ListPrec", pClienteCom.ListPrecios)
                    myComm.Parameters.AddWithValue("@Desc", pClienteCom.Descuento)
                    myComm.Parameters.AddWithValue("@Clasificacion", pClienteCom.Clasificacion)
                    If (pClienteCom.Vendedor IsNot Nothing) Then
                        myComm.Parameters.AddWithValue("@Vendedor", pClienteCom.Vendedor.IdVendedor)
                    Else
                        myComm.Parameters.AddWithValue("@Vendedor", DBNull.Value)
                    End If
                    myComm.Parameters.AddWithValue("@CtaCont", pClienteCom.CuentaContable)
                    'myComm.Parameters.AddWithValue("@SubCta", pClienteCom.SubCuenta)
                    myComm.Parameters.AddWithValue("@CtaAnt", pClienteCom.CuentaAnticipos)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaClienteCom(ByVal pClienteCom As ClientesComBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE clientescom " & _
                                            "SET LimCre = @LimCre, DiasCred = @DiasCred, MetPag = @MetPag, " & _
                                                "Banco = @Banco, CuentaBan = @CuentaBan, CliMay = @CliMay, " & _
                                                "ListPrec = @ListPrec, Descuento = @Desc, Clasificacion = @Clasificacion, " & _
                                                "Vendedor = @Vendedor, CtaCont = @CtaCont, CtaAnt = @CtaAnt " & _
                                                "WHERE IdCli = @IdCli ; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@LimCre", pClienteCom.LimiteCredito)
                    myComm.Parameters.AddWithValue("@DiasCred", pClienteCom.DiasCredito)
                    If (pClienteCom.MetodoPago IsNot Nothing) Then
                        myComm.Parameters.AddWithValue("@MetPag", pClienteCom.MetodoPago.IdMetodP)
                    Else
                        myComm.Parameters.AddWithValue("@MetPag", DBNull.Value)
                    End If
                    myComm.Parameters.AddWithValue("@Banco", pClienteCom.Banco)
                    myComm.Parameters.AddWithValue("@CuentaBan", pClienteCom.CuentaBancaria)
                    myComm.Parameters.AddWithValue("@CliMay", pClienteCom.ClienteMayoreo)
                    myComm.Parameters.AddWithValue("@ListPrec", pClienteCom.ListPrecios)
                    myComm.Parameters.AddWithValue("@Desc", pClienteCom.Descuento)
                    myComm.Parameters.AddWithValue("@Clasificacion", pClienteCom.Clasificacion)
                    If (pClienteCom.Vendedor IsNot Nothing) Then
                        myComm.Parameters.AddWithValue("@Vendedor", pClienteCom.Vendedor.IdVendedor)
                    Else
                        myComm.Parameters.AddWithValue("@Vendedor", DBNull.Value)
                    End If
                    myComm.Parameters.AddWithValue("@CtaCont", pClienteCom.CuentaContable)
                    'myComm.Parameters.AddWithValue("@SubCta", pClienteCom.SubCuenta)
                    myComm.Parameters.AddWithValue("@CtaAnt", pClienteCom.CuentaAnticipos)
                    myComm.Parameters.AddWithValue("@IdCli", pClienteCom.IdCli)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getClienteCom(ByVal IdCli As Integer) As ClientesComBE
        Dim tmpClienteCom As ClientesComBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdCli, LimCre, Saldo, DiasCred, " &
                                                "metodospago.IdEmpresa, metodospago.IdMetodP, metodospago.DesMetodoP, metodospago.Status, " &
                                                "Banco, CuentaBan, CliMay, ListPrec, Descuento, Clasificacion, " &
                                                "vendedores.IdVendedor, vendedores.Clave, vendedores.Nombre, " &
                                                "vendedores.Porc, vendedores.Status as vendStatus, " &
                                                "CtaCont, CtaAnt " & _
                                            "FROM clientescom LEFT JOIN metodospago " &
                                                        "ON (clientescom.MetPag = metodospago.IdMetodP) " &
                                                "LEFT JOIN vendedores " &
                                                        "ON (clientescom.Vendedor = vendedores.IdVendedor) " & _
                                            "WHERE IdCli = @IdCli ;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@IdCli", IdCli)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpClienteCom = ClientesComFact.Build(myDR)
                        Else
                            tmpClienteCom = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpClienteCom

        Catch ex As Exception
            Throw
        Finally
            tmpClienteCom = Nothing
        End Try
    End Function

#End Region

End Class
